package DBLayer;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBClothing implements IFDBClothing 
{
	private  Connection con;
	
	public DBClothing()
	{
		con = DBConnection1.getInstance().getDBcon();
	}
	
	private String buildQuery(String wClause)
	{
		String query = "SELECT * FROM Product AS p JOIN Clothing as c ON p.ProdID=c.ProdID ";
		if (wClause.length() > 0)
		{
			query = query + " WHERE " + wClause;
		}
		
		return query;
	}
	
	private Clothing buildClothing(ResultSet results)
	{
		Clothing clothingObj = new Clothing();
		try
		{
			clothingObj.setID(results.getInt(1));
			clothingObj.setSize(results.getString(2));
			clothingObj.setColour(results.getString(3));
			//System.out.println("Build clothing" + clothingObj.getName());
		}
		catch(Exception e)
		{
			System.out.println("Error in building the clothing object.");
		}
		return clothingObj;
	}
	
	private Clothing singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Clothing clothingObject = new Clothing();
		
		String query =  buildQuery(wClause);
		System.out.println(query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() )
			{
				clothingObject = buildClothing(results);
				stmt.close();
				if(retrieveAssociation)
	            {//the supplier is to be built as well
	            	IFDBSupplier dbSupplier=new DBSupplier();
	            	Supplier suppObj=dbSupplier.getSupplierByName(clothingObject.getSupplier().getName(), false);
	            	System.out.println("Supplier is selected.");
	            	clothingObject.setSupplier(suppObj); //supplier is selected
	            }
	            else
	            {
	            	clothingObject=null;
	            }
			}
		}
		catch(Exception e)
		{
			System.out.println("Query exception: "+e);
		}
		return clothingObject;
	}
	
	private ArrayList<Clothing> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Clothing> clothingList = new ArrayList<Clothing>();		
		String query = buildQuery(wClause);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while (results.next())
			{
				Clothing clothingObj = new Clothing();
				clothingObj = buildClothing(results);
				clothingList.add(clothingObj);
			}
			stmt.close();
			if(retrieveAssociation)
            {//the supplier is to be built as well
            	IFDBSupplier dbSupp=new DBSupplier();
            	for(Clothing clothingObj : clothingList)
            	{
            		int SuppID=clothingObj.getSupplier().getId();
            		Supplier suppObj=dbSupp.getSuppById(SuppID, false);
            		clothingObj.setSupplier(suppObj);
            		System.out.println("Supplier is selected.");
                }
            }
			
		}
		catch (Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return clothingList;
	}
	
	public int deleteClothing(int id)
    {
        int result=-1;
        String query = "DELETE FROM Clothing WHERE ProdID= '" + id + "'";    
        System.out.println(query);
        
        try
        {
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	result = stmt.executeUpdate(query);
        	stmt.close();
        }
        catch(Exception ex)
        {
        	System.out.println("Delete exception in clothing db: "+ex);
        }
        return(result);
    }

	public int updateClothing(Clothing clothing)
	{
		Clothing clothingObj = clothing;
		
		int result = -1;
		
		String query= "UPDATE Clothing SET " +
		"Size= '" + clothingObj.getSize() + "' " +
		"Colour= '" + clothingObj.getColour() + "'" +
		"WHERE ProdID= '" + clothingObj.getID() + "'";
		System.out.println("Update query:" + query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeUpdate(query);
			stmt.close();
		}
		
		catch (Exception ex)
		{
			System.out.println("Update exception in Clothing db: " + ex);
		}
		
		return(result);
	}
	
	public ArrayList<Clothing> getAllClothing(boolean retriveAssociation)
	{
		return miscWhere("", retriveAssociation);
	}
	
	public int insertClothing(Clothing clothing)
	{
		int result = -1;
		String query = "INSERT INTO Clothing (Size, Colour) VALUES ('" +
		//clothing.getID() + "', '" +
				clothing.getSize() + "', '" +
		clothing.getColour() + "')";
		
		System.out.println("insert : " + query);
		
		try
		{
			con.setAutoCommit(false);
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
			con.setAutoCommit(true);
		}
		catch(SQLException ex)
		{
			System.out.println("Clothing item not created.");
			System.out.println("Insert exception in Clothing db: "+ex);
			try
			{
				result=-1;
				con.rollback();
				con.setAutoCommit(true);
			}
			catch(Exception ex1)
			{
				System.out.println("Error rolling back "+ ex1);
			}
		}
		return(result);
	}
	
	@Override
	public ArrayList<Clothing> searchBySize(String size, boolean retrieveAssociation)
	{
		String wClause=" c.Size= '" + size + "'";
		return miscWhere(wClause, retrieveAssociation);
	}
	
	@Override
	public ArrayList<Clothing> searchByColour(String colour, boolean retrieveAssociation)
	{
		String wClause=" c.Colour= '" + colour + "'";
		return miscWhere(wClause, retrieveAssociation);
	}

	@Override
	public Clothing findClothingByID(int id, boolean retriveAssociation)
	{
		String wClause = " ProdID = '" + id + "'";
		return singleWhere(wClause, retriveAssociation);
	}
}